import logging, json
import re

from scrapy.utils.project import get_project_settings

# from TweetScraper.items import Tweet, User
from TweetScraper.items import Tweet
# from TweetScraper.utils import mkdirs
import pymysql
import time


logger = logging.getLogger(__name__)
SETTINGS = get_project_settings()
# 创建一个handler，用于写入日志文件
file_handler = logging.FileHandler(filename=SETTINGS['LOG_PATH'])
logger.addHandler(file_handler)

class SaveUserToDBPipelne(object):
    def __init__(self):
        self.mysql_conn = pymysql.connect(host=SETTINGS['MYSQL_HOST'],
                                     port=SETTINGS['MYSQL_PORT'],
                                     user=SETTINGS['MYSQL_USER'],
                                     password=SETTINGS['MYSQL_PASS'],
                                     db=SETTINGS['MYSQL_DB'])

        self.cursor = self.mysql_conn.cursor()

    def process_item(self, item, spider):
        description = self.mysql_conn.escape(item['description'])
        real_name = self.mysql_conn.escape(item['real_name'])
        sql = "INSERT INTO {} (user_id, username, follower, description) " \
              "VALUES ( {}, {}, '{}', {})" \
            .format('following_user', item['user_id'], real_name, item['follower'], description)
        try:
            self.cursor.execute(sql)
            self.mysql_conn.commit()
            logger.info("Add User %s to db", item['user_id'])
        except Exception as e:
            if 'Duplicate entry' in str(e):
                logger.info("User {} is already exist.".format(item['user_id']))
            else:
                logger.error(str(e))
            self.mysql_conn.rollback()

class SaveToFilePipeline(object):
    ''' pipeline that save data to disk '''

    def __init__(self):
        self.saveTweetPath = SETTINGS['SAVE_TWEET_PATH']
        self.saveUserPath = SETTINGS['SAVE_USER_PATH']
        # # mkdirs(self.saveTweetPath) # ensure the path exists
        # # mkdirs(self.saveUserPath)
        self.mysql_conn = pymysql.connect(host=SETTINGS['MYSQL_HOST'],
                                          port=SETTINGS['MYSQL_PORT'],
                                          user=SETTINGS['MYSQL_USER'],
                                          password=SETTINGS['MYSQL_PASS'],
                                          db=SETTINGS['MYSQL_DB'],
                                          charset="utf8mb4")
        self.cursor = self.mysql_conn.cursor()



    def process_item(self, item, spider):
        if isinstance(item, Tweet):
            content = item['raw_data']['full_text']
            content = self.mysql_conn.escape(content)
            content = self.remove_emoji(content)
            post_time = item['raw_data']['created_at']
            post_time = time.strftime('%Y-%m-%d %H:%M:%S', time.strptime(post_time, "%a %b %d %H:%M:%S +0000 %Y"))
            url = "https://twitter.com/{}/status/{}".format(item["raw_data"]["user_id_str"], item["id_"])
            source = item['source']
            real_name = item['real_name']
            keyword = item['keyword']
            # item = {content, post_time, url, source, keyword}
            # print(item)
            sql = "INSERT INTO {} (content, real_name, pub_date, url, user_id, source) " \
                  "VALUES ( {}, '{}', '{}', '{}', '{}', '{}')" \
                .format(SETTINGS["MYSQL_TABLE_NAME_RESULT"], content, real_name, post_time, url, keyword, source)
            isAdded = False  #
            try:
                self.cursor.execute(sql)
                self.mysql_conn.commit()
                isAdded = True  #
                logger.info("Add tweet:%s" % item['id_'])
            except Exception as e:
                if 'Duplicate entry' in str(e):
                    logger.info("Tweet {} is already exist.".format(item["id_"]))
                else:
                    logger.info('Before to save url [{}], accounting a problem:'.format(url))
                    content = self.remove_emoji(content)
                    logger.info(str(e))
                self.mysql_conn.rollback()

            # if isAdded:  #
            #     sql = "UPDATE main a,ttopic b SET a.topicname = b.topicname WHERE a.topicname IS NULL " \
            #           "AND (b.topicwords LIKE CONCAT('%|',a.keyword,'|%') " \
            #           "OR b.topicwords LIKE CONCAT(a.keyword,'|%') " \
            #           "OR b.topicwords LIKE CONCAT('%|',a.keyword)); "
            #     try:
            #         self.cursor.execute(sql)
            #         self.mysql_conn.commit()
            #         logger.info("Update topic name of tweet:%s" % item['id_'])
            #     except Exception:
            #         self.mysql_conn.rollback()

        # elif isinstance(item, User):
        #     savePath = os.path.join(self.saveUserPath, item['id_'])
        #     if os.path.isfile(savePath):
        #         pass # simply skip existing items
        #         # logger.debug("skip user:%s"%item['id_'])
        #         ### or you can rewrite the file, if you don't want to skip:
        #         # self.save_to_file(item,savePath)
        #         # logger.debug("Update user:%s"%item['id_'])
        #     else:
        #         self.save_to_file(item, savePath)
        #         logger.debug("Add user:%s" %item['id_'])

        else:
            logger.debug("Item type is not recognized! type = %s" %type(item))


    def save_to_file(self, item, fname):
        ''' input: 
                item - a dict like object
                fname - where to save
        '''
        with open(fname, 'w', encoding='utf-8') as f:
            json.dump(dict(item), f, ensure_ascii=False)
    def remove_emoji(self, content):
        # compile_emoji = re.compile("(?:[\uD83C\uDF00-\uD83D\uDDFF]|[\uD83E\uDD00-\uD83E\uDDFF]|[\uD83D\uDE00-\uD83D\uDE4F]|[\uD83D\uDE80-\uD83D\uDEFF]|[\u2600-\u26FF]\uFE0F?|[\u2700-\u27BF]\uFE0F?|\u24C2\uFE0F?|[\uD83C\uDDE6-\uD83C\uDDFF]{1,2}|[\uD83C\uDD70\uD83C\uDD71\uD83C\uDD7E\uD83C\uDD7F\uD83C\uDD8E\uD83C\uDD91-\uD83C\uDD9A]\uFE0F?|[\u0023\u002A\u0030-\u0039]\uFE0F?\u20E3|[\u2194-\u2199\u21A9-\u21AA]\uFE0F?|[\u2B05-\u2B07\u2B1B\u2B1C\u2B50\u2B55]\uFE0F?|[\u2934\u2935]\uFE0F?|[\u3030\u303D]\uFE0F?|[\u3297\u3299]\uFE0F?|[\uD83C\uDE01\uD83C\uDE02\uD83C\uDE1A\uD83C\uDE2F\uD83C\uDE32-\uD83C\uDE3A\uD83C\uDE50\uD83C\uDE51]\uFE0F?|[\u203C\u2049]\uFE0F?|[\u25AA\u25AB\u25B6\u25C0\u25FB-\u25FE]\uFE0F?|[\u00A9\u00AE]\uFE0F?|[\u2122\u2139]\uFE0F?|\uD83C\uDC04\uFE0F?|\uD83C\uDCCF\uFE0F?|[\u231A\u231B\u2328\u23CF\u23E9-\u23F3\u23F8-\u23FA]\uFE0F?)", )
        compile_emoji = re.compile('['
                                   '\U0001F300-\U0001F5FF'  # 🌀 --> 🗿
                                   '\U0001F900-\U0001F9FF'  # 🤀 --> 🧿
                                   '\U0001F600-\U0001F64F'  # 😀 --> 🙏
                                   '\U0001F680-\U0001F6FF'  # 🚀-\U0001f6ff
                                   '\u2600-\u26FF'  # ☀ --> ⛿
                                   '\u2700-\u27BF'  # ✀ --> ➿
                                   '\u24C2'  # Ⓜ
                                   '\U0001F1E6-\U0001F1FF'  # 🇦-🇿
                                   '\U0001F170-\U0001F171'  # 🅰-🅱
                                   '\U0001F17E-\U0001F17F'  # 🅾-🅿
                                   '\U0001F18E'  # 🆎
                                   '\U0001F191-\U0001F19A'  # 🆑-🆚

                                   # '\u0023'  # #
                                   # '\u002A'  # *
                                   # '\u0030-\u0039'  # 0 --> 9

                                   '\u2194-\u2199'  # ↔ --> ↙
                                   '\u21A9-\u21AA'  # ↩ --> ↪
                                   '\u2B05-\u2B07'  # ⬅ --> ⬇
                                   '\u2B1B'  # ⬛
                                   '\u2B1C'  # ⬜
                                   '\u2B50'  # ⭐
                                   '\u2B55'  # ⭕
                                   '\u2934-\u2935'  # ⤴ --> ⤵
                                   '\u3030'  # 〰
                                   '\u303D'  # 〽
                                   '\u3297'  # ㊗
                                   '\u3299'  # ㊙
                                   '\U0001F201-\U0001F202'  # 🈁 --> 🈂
                                   '\U0001F21A'  # 🈚
                                   '\U0001F22F'  # 🈯
                                   '\U0001F232-\U0001F23A'  # 🈲 --> 🈺
                                   '\U0001F250-\U0001F251'  # 🉐 --> 🉑
                                   '\u203C'  # ‼
                                   '\u2049'  # ⁉
                                   '\u25AA'  # ▪
                                   '\u25AB'  # ▫
                                   '\u25B6'  # ▶
                                   '\u25C0'  # ◀
                                   '\u25FB-\u25FE'  # ◻ --> ◾
                                   '\u00A9'  # ©
                                   '\u00AE'  # ®
                                   '\u2122'  # ™
                                   '\u2139'  # ℹ
                                   '\U0001F004'  # 🀄
                                   '\U0001F0CF'  # 🃏
                                   '\u231A'  # ⌚
                                   '\u231B'  # ⌛
                                   '\u2328'  # ⌨
                                   '\u23CF'  # ⏏
                                   '\u23E9-\u23F3'  # ⏩ --> ⏳
                                   '\u23F8-\u23FA'  # ⏸-⏺
                                   ']',
                                   re.UNICODE)
        return compile_emoji.sub(' ', content)